package com.plsData.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.plsDTO.dataObjects.Employee;
import com.plsDTO.dataObjects.EmployeeWorkstation;
import com.plsDTO.dataObjects.Seat;

public class EmployeeData extends Data {
//gets details of employees for a particular quadrant
	public ArrayList<EmployeeWorkstation> getEmployeesByQuadrant(String quadrant) {
		ArrayList<EmployeeWorkstation> employeeWorkstation = new ArrayList<EmployeeWorkstation>();
		try {
			Connection dbConnection = null;
			PreparedStatement preparedStatement = null;

			if (dbConnection == null) {

				dbConnection = getConnection();
			}
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT seat_code, seat.local_no, employee.employee_id,"
					+ "employee.first_name,employee.last_name FROM SEAT"
					+ " LEFT JOIN employee on employee.employee_id = seat.employee_id"
					+ " WHERE seat.seat_code like ?");

			preparedStatement = dbConnection.prepareStatement(sb.toString());
			preparedStatement.setString(1, quadrant + "%");

			ResultSet result = preparedStatement.executeQuery();

			EmployeeWorkstation tempWorkStation;

			while (result.next()) {
				tempWorkStation = new EmployeeWorkstation();

				Employee employeeDetails = new Employee();

				employeeDetails.setFirstName(result.getString("first_name"));
				employeeDetails.setLastName(result.getString("last_name"));
				employeeDetails.setEmployeeId(result.getInt("employee_id"));

				Seat seatDetails = new Seat();
				seatDetails.setSeatCode(result.getString("seat_code"));
				seatDetails.setLocalNumber(result.getInt("local_no"));

				tempWorkStation.setSeatDetails(seatDetails);
				tempWorkStation.setEmployee(employeeDetails);
				employeeWorkstation.add(tempWorkStation);

			}
			return employeeWorkstation;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return employeeWorkstation;
	}
//updates employee seat given an employeeid and seatcode
	public boolean updateEmployeeSeat(int employeeId, String seatCode) {
		int successful = 0;
		Connection dbConnection = null;
		try {
			PreparedStatement preparedStatement = null;

			if (dbConnection == null) {

				dbConnection = getConnection();

			}
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE seat SET employee_id = IF(employee_id IS NULL, ?, employee_id)"
					+ " WHERE seat_code = ?");

			preparedStatement = dbConnection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, employeeId);
			preparedStatement.setString(2, seatCode);
			successful = preparedStatement.executeUpdate();
			dbConnection.commit();

		} catch (SQLException e) {
			try {
				dbConnection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				return false;
			}
			return false;
		} finally {
			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					return false;
				}
			}
		}

		return successful > 0;
	}
	//deletes employee seat given an employeeid and seatcode
	public boolean deleteEmployeeSeat(int employeeId, String seatCode) {

		int affectedRows = 0;
		Connection dbConnection = null;
		try {

			PreparedStatement preparedStatement = null;

			if (dbConnection == null) {

				dbConnection = getConnection();

			}
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE seat SET employee_id = null WHERE employee_id = ? and seat_code = ? ");

			preparedStatement = dbConnection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, employeeId);
			preparedStatement.setString(2, seatCode);
			affectedRows = preparedStatement.executeUpdate();

		} catch (SQLException e) {
			try {
				dbConnection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				return false;
			}
			return false;
		} finally {
			if (dbConnection != null) {
				try {
					dbConnection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					return false;
				}
			}
		}

		return affectedRows > 0;

	}
//gets employee details by employeedId
	public EmployeeWorkstation searchEmployeeById(int employeeId) {
		EmployeeWorkstation empWS = new EmployeeWorkstation();

		try {
			Connection dbConnection = null;
			PreparedStatement preparedStatement = null;

			if (dbConnection == null) {

				dbConnection = getConnection();

			}
			StringBuffer sb = new StringBuffer();
			sb.append("select seat.employee_id, seat.local_no, seat.quadrant, seat_type.seat_type_desc,"
					+ " seat.seat_code, employee.first_name, employee.last_name from seat "
					+ " inner join employee on employee.employee_id =seat.employee_id "
					+ " inner join seat_type on seat_type.seat_type_id = seat.seat_type_id "
					+ "where employee.employee_id = ?");

			preparedStatement = dbConnection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, employeeId);

			ResultSet result = preparedStatement.executeQuery();

			

			while (result.next()) {

				Employee employeeDetails = new Employee();
				employeeDetails.setFirstName(result.getString("first_name"));
				employeeDetails.setLastName(result.getString("last_name"));
				employeeDetails.setEmployeeId(result.getInt("employee_id"));
				//
				Seat seatDetails = new Seat();
				seatDetails.setSeatCode(result.getString("seat_code"));
				seatDetails.setQuadrant(result.getString("quadrant"));
				seatDetails.setLocalNumber(result.getInt("local_no"));
				seatDetails.setSeatType(result.getString("seat_type_desc"));

				empWS.setEmployee(employeeDetails);
				empWS.setSeatDetails(seatDetails);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return empWS;
	}

}
